/**
 *
 * The MIT License
 *
 * Copyright (c) 2008 Board of Trustees, Leland Stanford Jr. University. All rights reserved.
 *
 * Permission is hereby granted, free of charge, to any person obtaining a copy
 * of this software and associated documentation files (the "Software"), to deal
 * in the Software without restriction, including without limitation the rights
 * to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
 * copies of the Software, and to permit persons to whom the Software is
 * furnished to do so, subject to the following conditions:

 * The above copyright notice and this permission notice shall be included in
 * all copies or substantial portions of the Software.

 * THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
 * IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
 * FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
 * AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
 * LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
 * OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
 * THE SOFTWARE.
 */
package org.macdadi.core.server;

import com.google.gwt.user.server.rpc.RemoteServiceServlet;
import org.macdadi.core.client.auth.AuthService;
import org.macdadi.core.client.auth.AuthorizationException;
import org.macdadi.core.client.domain.*;
import com.mysql.jdbc.Driver;

import java.io.IOException;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Properties;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 * @author Ben Suter <ben.suter@gmail.com>
 */
public class DataServiceImpl extends RemoteServiceServlet implements DataService
{
    protected static final String COL_ID = "id";
    protected static final String COL_NAME = "name";
    protected static final String COL_OBJ_ID = "object_id";

    protected transient Connection db;
    protected final String dbPropertiesFile = "database.properties";
    protected final String dbPropertiesDriverClass = "driverclass";
    protected final String dbPropertiesConnectionString = "connectionstring-read";

    protected transient AuthService authService;

    public DataServiceImpl()
    {
        super();

        /* Setup the DB connection */
        try {
            Properties dbProps = new Properties();
            dbProps.load(getClass().getResourceAsStream("database.properties"));

            Class.forName(dbProps.getProperty(dbPropertiesDriverClass));
            db = DriverManager.getConnection(dbProps.getProperty(dbPropertiesConnectionString));
            authService = new AuthServiceImpl(); // TODO: tried passing in db ref, but uses different user!
        } catch (IOException ex) {
            Logger.getLogger(DataServiceImpl.class.getName()).log(Level.SEVERE, null, ex);
        } catch (ClassNotFoundException ex) {

            Logger.getLogger(DataServiceImpl.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
            Logger.getLogger(DataServiceImpl.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public ExtendedProjectInfo getProject(int id)
    {
        ExtendedProjectInfo pi;

        try {
            StringBuilder sb = new StringBuilder();
            sb.append("SELECT P.id, P.name, P.is_public, P.url_path, P.allow_copy, ");
            sb.append(" ( SELECT count(user_id) FROM project_members WHERE project_id = ? AND is_active = 1) AS member_count, ");
            sb.append(" ( SELECT count(id) AS goal_count FROM goals WHERE project_id = ? ) AS goal_count, ");
            sb.append(" ( SELECT count(id) AS option_count FROM options WHERE project_id = ? ) AS option_count ");
            sb.append(" FROM projects P WHERE P.id = ? ORDER BY id; ");

            PreparedStatement s = db.prepareStatement(sb.toString(), ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
            s.setInt(1, id);
            s.setInt(2, id);
            s.setInt(3, id);
            s.setInt(4, id);

            ResultSet r = s.executeQuery();
            if (r.next()) {
                pi = new ExtendedProjectInfo();
                pi.setId(id);
                pi.setName(r.getString(COL_NAME));
                pi.setPublic(r.getBoolean(3));
                pi.setUrlPath(r.getString(4));
                pi.setAllowCopy(r.getBoolean(5));
                pi.setNumProjectMembers(r.getInt(6));
                pi.setNumGoals(r.getInt(7));
                pi.setNumOptions(r.getInt(8));
            } else {
                pi = null;
            }
            s.close();
        } catch (SQLException ex) {
            Logger.getLogger(DataServiceImpl.class.getName()).log(Level.SEVERE, null, ex);
            pi = null;
        }

        return pi;
    }

    /**
     * Get the hierarchical goals for the specified project. The resulting array
     * contains the root goals (those without parents), each of which has
     * a set of children, etc.
     *
     * @param projectId id of the project
     * @param authToken session identifier
     * @return the root goals of the hierarchy
     */
    public Goal[] getGoals(int projectId, String authToken) throws AuthorizationException
    {
        checkAuthorization(authToken, projectId);

        //Goal[] roots = generateSampleGoals();
        //return roots;

        /**
         * 1. Request all goals from the database.
         * 2. a. Store each goal in the "goals" map. At this point they do not have references
         * to their parent or children.
         * 2. b. Any goal with no parent is a root, so add it to the "roots" list.
         * 2. c. Temporarily store a mapping for each non-root goal to the id of its parent.
         * 3. Iterate through all the non-root goals. For each one, add it as a child to the
         * specified parent, and give it a reference to its parent.
         */
        ArrayList<Goal> roots = new ArrayList<Goal>();
        HashMap<Integer, Goal> goals = new HashMap<Integer, Goal>();
        HashMap<Goal, Integer> childToParent = new HashMap<Goal, Integer>();

        try {
            Goal g;
            Integer parentId;
            Integer id;
            String q = "SELECT id, name, parent_goal FROM goals WHERE project_id = " + projectId + " ORDER BY parent_goal, name";
            Statement s = db.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
            ResultSet r = s.executeQuery(q);
            while (r.next()) {
                g = new Goal();
                id = r.getInt(COL_ID);
                g.setId(id);
                g.setName(r.getString(COL_NAME));
                parentId = r.getInt("parent_goal");
                goals.put(id, g);
                if (parentId > 0)
                    childToParent.put(g, parentId);
                else
                    roots.add(g);
            }
            s.close();
        } catch (SQLException ex) {
            Logger.getLogger(DataServiceImpl.class.getName()).log(Level.SEVERE, null, ex);
        }

        /* Add references to children and parent */
        Goal parent;
        Integer parentId;
        for (Goal child : childToParent.keySet()) {
            parentId = childToParent.get(child);
            parent = goals.get(parentId);
            child.setParent(parent);
            parent.addChild(child);
        }

        return roots.toArray(new Goal[roots.size()]);
    }
    
    public Goal[] getLeafGoals(int projectId, String authToken) throws AuthorizationException
    {
    	checkAuthorization(authToken, projectId);

        ArrayList<Goal> leafs = new ArrayList<Goal>();

        try {
            Goal g;
            Integer parentId;
            Integer id;
            String q = "SELECT id, name, parent_goal FROM goals WHERE project_id = " + projectId + " AND is_leaf=1 ORDER BY parent_goal, name";
            Statement s = db.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
            ResultSet r = s.executeQuery(q);
            while (r.next()) {
                g = new Goal();
                id = r.getInt(COL_ID);
                g.setId(id);
                g.setName(r.getString(COL_NAME));
                parentId = r.getInt("parent_goal");
                leafs.add(g);
            }
            s.close();
        } catch (SQLException ex) {
            Logger.getLogger(DataServiceImpl.class.getName()).log(Level.SEVERE, null, ex);
        }

        return leafs.toArray(new Goal[leafs.size()]);
    }

    public WeightedGoal[] getLeafGoalWeights(int projectId, String authToken) throws AuthorizationException
    {
        checkAuthorization(authToken, projectId);

        ArrayList<WeightedGoal> goalLeafs = new ArrayList<WeightedGoal>();

        try {
            WeightedGoal g;
            StringBuilder qb = new StringBuilder();
            qb.append("SELECT G.id, G.name, G.parent_goal, ");
            qb.append("IFNULL(SUM(P.value), 0) / count(DISTINCT user_id) As weight ");
            qb.append("FROM goals G ");
            qb.append("LEFT JOIN preferences P ON P.goal_id = G.id ");
            qb.append("WHERE G.project_id = ").append(projectId).append(" AND G.is_leaf = 1 ");
            qb.append("GROUP BY G.id ");
            qb.append("ORDER BY G.parent_goal, G.name;");
            Statement s = db.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
            ResultSet r = s.executeQuery(qb.toString());
            while (r.next()) {
                g = new WeightedGoal();
                g.setId(r.getInt(COL_ID));
                g.setName(r.getString(COL_NAME));
                g.setWeight(r.getDouble("weight"));
                goalLeafs.add(g);
            }
            s.close();
        } catch (SQLException ex) {
            Logger.getLogger(DataServiceImpl.class.getName()).log(Level.SEVERE, null, ex);
        }

        return goalLeafs.toArray(new WeightedGoal[goalLeafs.size()]);
    }

    /**
     * Utility method for testing without the database. Generates a small tree of dummy goals.
     *
     * @return the roots of a tree
     */
    protected Goal[] generateSampleGoals()
    {
        Goal[] roots = new Goal[2];

        Goal g100 = new Goal();
        g100.setId(100);
        g100.setName("Goal 100");
        roots[0] = g100;

        Goal g110 = new Goal();
        g110.setId(110);
        g110.setName("Goal 110");
        g100.addChild(g110);

        Goal g200 = new Goal();
        g200.setId(200);
        g200.setName("Goal 200");
        roots[1] = g200;

        return roots;
    }

    /**
     * Return a list of all options for the specified project.
     *
     * @param projectId if of the project
     * @param authToken session identifier
     * @return an array of options
     */
    public Option[] getOptions(int projectId, String authToken) throws AuthorizationException
    {
    	checkAuthorization(authToken, projectId);

        ArrayList<Option> roots = new ArrayList<Option>();
        HashMap<Integer, Option> options = new HashMap<Integer, Option>();
        HashMap<Option, Integer> childToParent = new HashMap<Option, Integer>();

        try {
            Option o;
            Integer parentId;
            Integer id;
            String q = "SELECT id, name, parent_option FROM options WHERE project_id = " + projectId + " ORDER BY parent_option, name";
            Statement s = db.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
            ResultSet r = s.executeQuery(q);
            while (r.next()) {
                o = new Option();
                id = r.getInt(COL_ID);
                o.setId(id);
                o.setName(r.getString(COL_NAME));
                parentId = r.getInt("parent_option");
                options.put(id, o);
                if (parentId > 0)
                    childToParent.put(o, parentId);
                else
                    roots.add(o);
            }
            s.close();
        } catch (SQLException ex) {
            Logger.getLogger(DataServiceImpl.class.getName()).log(Level.SEVERE, null, ex);
        }

        /* Add references to children and parent */
        Option parent;
        Integer parentId;
        for (Option child : childToParent.keySet()) {
            parentId = childToParent.get(child);
            parent = options.get(parentId);
            child.setParent(parent);
            parent.addChild(child);
        }

        return roots.toArray(new Option[roots.size()]);
    }
    
    public Option[] getLeafOptions(int projectId, String authToken) throws AuthorizationException
    {
 	   checkAuthorization(authToken, projectId);
 	   
 	   ArrayList<Option> leafOptions = new ArrayList<Option>();
 	   
 	   try  {
 		   Option o;
 		   String q = "SELECT id, name FROM options WHERE project_id = "+ projectId +" AND is_leaf = 1"; //returns all leaf nodes...
 		   Statement s = db.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
 		   ResultSet r = s.executeQuery(q);
 		   while ( r.next() ) {
                o = new Option();
                o.setId(r.getInt(COL_ID));
                o.setName(r.getString(COL_NAME));
                leafOptions.add(o);
            }
            s.close();
        } catch (SQLException ex) {
            Logger.getLogger(DataServiceImpl.class.getName()).log(Level.SEVERE, null, ex);
        }
        return leafOptions.toArray(new Option[leafOptions.size()]);
    }

    /**
     * Return a list of all stakeholder groups, each containing zero or more users
     * as members.
     *
     * @param projectId the project whose stakeholders we want
     * @param authToken session identifier
     * @return an array of StakeholderGroups for the specified project
     */
    public StakeholderGroup[] getStakeholderGroups(int projectId, String authToken) throws AuthorizationException
    {
        checkAuthorization(authToken, projectId);

        HashMap<Integer, StakeholderGroup> groups = new HashMap<Integer, StakeholderGroup>();

        try {
            User u;
            StakeholderGroup sg;
            Integer sgId;
            Integer uId;
            StringBuilder qb = new StringBuilder();
            qb.append("SELECT SG.id AS sg_id, SG.name AS sg_name, U.name AS user_name, user_id ");
            qb.append("FROM stakeholder_groups SG ");
            qb.append("LEFT JOIN stakeholder_group_members SGM ON SGM.sg_id = SG.id ");
            qb.append("LEFT JOIN users U ON U.id = SGM.user_id ");
            qb.append("WHERE SG.project_id = ").append(projectId).append(" ");
            qb.append("ORDER BY sg_id ");
            Statement s = db.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
            ResultSet r = s.executeQuery(qb.toString());
            while (r.next()) {
                sgId = r.getInt("sg_id");
                if (groups.containsKey(sgId)) {
                    sg = groups.get(sgId);
                } else {
                    sg = new StakeholderGroup();
                    sg.setId(sgId);
                    sg.setName(r.getString("sg_name"));
                    groups.put(sgId, sg);
                }

                uId = r.getInt("user_id");
                if (uId > 0) {
                    u = new User();
                    u.setId(uId);
                    u.setName(r.getString("user_name"));
                    sg.addMember(u);
                }
            }
            s.close();
        } catch (SQLException ex) {
            Logger.getLogger(DataServiceImpl.class.getName()).log(Level.SEVERE, null, ex);
        }

        return groups.values().toArray(new StakeholderGroup[groups.size()]);
    }

    /**
     * Return a list of preferences for the specified user and project. Each
     * Preference has a Goal reference, but note that these Goal objects do not
     * have parent and children information. All the goals are "leaf" nodes for now.
     *
     * @param projectId id of the project
     * @param userId    id of the user
     * @param authToken session identifier
     * @return a list of preferences ordered by goal name and id
     */
    public Preference[] getPreferencesForUser(int projectId, int userId, String authToken) throws AuthorizationException
    {
        checkAuthorization(authToken, projectId);

        ArrayList<Preference> prefs = new ArrayList<Preference>();

        try {
            User u;
            Goal g;
            Integer value;
            Preference pref;
            StringBuilder qb = new StringBuilder();
            /* NOTE: Need the P.user_id = x condition to be in the JOIN's ON clause, not the WHERE */
            qb.append("SELECT P.value, G.id AS goal_id, G.name AS goal_name, U.name AS user_name ");
            qb.append("FROM goals G ");
            qb.append("LEFT JOIN preferences P ON G.id = P.goal_id ");
            qb.append("AND P.user_id = ").append(userId).append(" ");
            qb.append("LEFT JOIN users U ON U.id = P.user_id ");
            qb.append("WHERE G.is_leaf = 1 ");
            qb.append("AND G.project_id = ").append(projectId).append(" ");
            qb.append("ORDER BY G.name, G.id ");
            Statement s = db.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
            ResultSet r = s.executeQuery(qb.toString());
            while (r.next()) {
                pref = new Preference();
                value = r.getInt("value");
                if (r.wasNull())
                    value = 0;  // TODO: decide whether this should be -1 when null, i.e. no pref set yet
                pref.setValue(value);

                g = new Goal();
                g.setId(r.getInt("goal_id"));
                g.setName(r.getString("goal_name"));
                pref.setGoal(g);

                u = new User(); // TODO: this is the same user for each record, use two result sets!
                u.setId(userId);
                u.setName(r.getString("user_name")); // TODO: Fix this - it is probably null whenever no row is present (pref value is considered zero)
                pref.setUser(u);

                prefs.add(pref);
            }
            s.close();
        } catch (SQLException ex) {
            Logger.getLogger(DataServiceImpl.class.getName()).log(Level.SEVERE, null, ex);
        }

        return prefs.toArray(new Preference[prefs.size()]);
    }

    public int[][] getPreferencesForAllUsers(int projectId, User[] user, String authToken) throws AuthorizationException
    {
        checkAuthorization(authToken, projectId);
        
        ArrayList<Preference[]> user_prefs = new ArrayList<Preference[]>();

        for(int i = 0; i < user.length; i++) {
        	user_prefs.add((Preference[])getPreferencesForUser(projectId, user[i].getId(), authToken));
        }
        
        int[][] values = new int[user.length][user_prefs.get(0).length];
        
        for(int i = 0; i < user.length; i++) {
        	for(int j = 0; j < user_prefs.get(i).length; j++) {
        		if(user_prefs.get(i)[j].getUser().getName() == null) {
        			values[i][j] = 0;
        		} else {
        			values[i][j] = user_prefs.get(i)[j].getValue();
        		}
        	}
        }

        return values;
    }
    
    /**
     * Return all Impacts for the specified project, in the form of a matrix. This matrix
     * has two dimensions, Goal and Option.
     *
     * @param projectId the project of interest
     * @param authToken session identifier
     * @return a 2D matrix of Impacts, indexed by Goal and Option
     */
    public ImpactMatrix getImpacts(int projectId, String authToken) throws AuthorizationException
    {
        checkAuthorization(authToken, projectId);

        // not the most efficient way to do this, but let's give it a try
        ImpactMatrix matrix = new ImpactMatrix();

        Impact imp;
        WeightedGoal g = null;
        Option o = null;
        int value = 0;

        try {
        	//Obtains all of the leaf Goals
            ArrayList<WeightedGoal> goalLeafs = new ArrayList<WeightedGoal>();
            StringBuilder qb = new StringBuilder();
            qb.append("SELECT G.id, G.name, G.parent_goal, ");
            qb.append("IFNULL(SUM(P.value), 0) / count(DISTINCT user_id) As weight ");
            qb.append("FROM goals G ");
            qb.append("LEFT JOIN preferences P ON P.goal_id = G.id ");
            qb.append("WHERE G.project_id = ").append(projectId).append(" AND G.is_leaf = 1 ");
            qb.append("GROUP BY G.id ");
            qb.append("ORDER BY G.parent_goal, G.name;");
            Statement s = db.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
            ResultSet r = s.executeQuery(qb.toString());
            while (r.next()) {
                g = new WeightedGoal();
                g.setId(r.getInt(COL_ID));
                g.setName(r.getString(COL_NAME));
                g.setWeight(r.getDouble("weight"));
                goalLeafs.add(g);
            }
            s.close();

            WeightedGoal[] allGoals = goalLeafs.toArray(new WeightedGoal[goalLeafs.size()]);
            goalLeafs.clear();
            goalLeafs = null;

            //Obtains all of the leaf options
            Option[] allOptions = getLeafOptions(projectId, authToken);
            for (int gi = 0; gi < allGoals.length; gi++) {
                for (int oi = 0; oi < allOptions.length; oi++) {
                    imp = new Impact();
                    imp.setGoal(allGoals[gi]);
                    imp.setOption(allOptions[oi]);
                    imp.setValue(0);
                    // TODO: (could set some other flag here if unset!), i.e. imp.setNotSetYet(true);
                    matrix.addImpact(imp);
                }
            }
            allGoals = null;
            //allOptions = null;
            g = null;
            o = null;
            imp = null;
            
            /* Now get all existing impact values from the DB (all those that are non-zero ?) */
            qb = new StringBuilder();
            qb.append("SELECT G.id AS goal_id, G.name AS goal_name, I.value, O.id AS option_id, O.name AS option_name ");
            qb.append("FROM goals G ");
            qb.append("LEFT JOIN impacts I ON I.goal_id = G.id ");
            qb.append("LEFT JOIN options O ON O.id = I.option_id ");
            qb.append("WHERE G.is_leaf = 1 AND O.is_leaf = 1 ");
            qb.append("AND G.project_id = ").append(projectId).append(" ");
            qb.append("AND I.goal_id IS NOT NULL ");
            qb.append("ORDER BY G.id, O.id ");
            s = db.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
            r = s.executeQuery(qb.toString());
            while (r.next()) {
                /* We just need the goal and option to each have their id fields */
                g = new WeightedGoal();
                g.setId(r.getInt("goal_id"));

                o = new Option();
                o.setId(r.getInt("option_id"));

                imp = matrix.getImpact(g, o); //here's the error

                value = r.getInt("value");
                if (r.wasNull())
                    value = 0;

                imp.setValue(value);
            }
            s.close();
        } catch (Exception ex) {
            String msg = "Error getting impact for G: " + g.getId() + " O: " + o.getId() + " v: " + value;
            log(msg, null);
            Logger.getLogger(DataServiceImpl.class.getName()).log(Level.SEVERE, msg, ex);
        }

        return matrix;
    }


    /**
     * Return an aggregate list of preferences for the specified user and project. Each
     * Preference has a Goal reference, but note that these Goal objects do not
     * have parent and children information.
     * <p/>
     * The preference values for each leaf goal are summed and assigned to the relevant
     * parent goal. The list of preferences returned contains one item for each goal
     * that has leaf children.
     *
     * @param projectId id of the project
     * @param userId    id of the user
     * @param authToken session identifier
     * @return a list of aggregate goal preferences
     */
    public Preference[] getAggregatePreferencesForUser(int projectId, int userId, String authToken) throws AuthorizationException
    {
        checkAuthorization(authToken, projectId);

        ArrayList<Preference> prefs = new ArrayList<Preference>();

        try {
            User u;
            Goal g;
            Integer value;
            Preference pref;

            Statement sUser = db.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
            ResultSet rUser = sUser.executeQuery("SELECT name FROM users WHERE id = " + userId + " ");
            u = new User();
            u.setId(userId);
            if (rUser.next()) {
                u.setName(rUser.getString("name"));
            }

            StringBuilder qb = new StringBuilder();
            qb.append("SELECT SUM(P.value) AS sum_value, PG.name AS goal_name, PG.id AS goal_id ");
            qb.append("FROM preferences P ");
            qb.append("LEFT JOIN goals G ON G.id = P.goal_id ");
            qb.append("LEFT JOIN goals PG ON PG.id = G.parent_goal ");
            qb.append("WHERE G.project_id = ").append(projectId).append(" ");
            qb.append("AND P.user_id = ").append(userId).append(" ");
            qb.append("GROUP BY G.parent_goal ");
            qb.append("ORDER BY sum_value DESC ");
            Statement s = db.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
            ResultSet r = s.executeQuery(qb.toString());
            while (r.next()) {
                pref = new Preference();
                value = r.getInt("sum_value");
                if (r.wasNull())
                    value = -1;
                pref.setValue(value);

                g = new Goal();
                g.setId(r.getInt("goal_id"));
                g.setName(r.getString("goal_name"));
                pref.setGoal(g);

                pref.setUser(u);

                prefs.add(pref);
            }
            s.close();
        } catch (SQLException ex) {
            Logger.getLogger(DataServiceImpl.class.getName()).log(Level.SEVERE, null, ex);
        }

        return prefs.toArray(new Preference[prefs.size()]);
    }

    /**
     * Return a list of all members of the specified project.
     *
     * @param projectId         if of the project
     * @param onlyActiveMembers if true, then members of the project who are marked
     *                          as inactive will not be included
     * @param authToken         session identifier
     * @return an array of users
     */
    public User[] getGateKeepers(int projectId, boolean onlyActiveMembers, int excludeStakeholderGroup, String authToken) throws AuthorizationException
    {
        checkAuthorization(authToken, projectId);

        ArrayList<User> members = new ArrayList<User>();
        try {
            User u;
            String q = "SELECT U.id, U.name FROM project_members PM LEFT JOIN users U ON U.id = PM.user_id ";
            q += "LEFT JOIN role_members RM ON RM.user_id = PM.user_id AND RM.project_id = PM.project_id ";
            q += "LEFT JOIN roles R ON R.id = RM.role_id ";
            q += "WHERE PM.project_id = " + projectId + " ";
            q += "AND R.name = 'gatekeeper' ";
            q += "ORDER BY U.name";
            Statement s = db.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
            ResultSet r = s.executeQuery(q);
            while (r.next()) {
                u = new User();
                u.setId(r.getInt(COL_ID));
                u.setName(r.getString(COL_NAME));
                members.add(u);
            }
            s.close();
        } catch (SQLException ex) {
            Logger.getLogger(DataServiceImpl.class.getName()).log(Level.SEVERE, null, ex);
        }
        return members.toArray(new User[members.size()]);
    }

    public User[] getDesigners(int projectId, String authToken) throws AuthorizationException
    {
        checkAuthorization(authToken, projectId);

        ArrayList<User> members = new ArrayList<User>();
        try {
            User u;
            String q = "SELECT U.id, U.name FROM project_members PM LEFT JOIN users U ON U.id = PM.user_id ";
            q += "LEFT JOIN role_members RM ON RM.user_id = PM.user_id AND RM.project_id = PM.project_id ";
            q += "LEFT JOIN roles R ON R.id = RM.role_id ";
            q += "WHERE PM.project_id = " + projectId + " ";
            q += "AND R.name = 'designer' ";
            q += "ORDER BY U.name";
            Statement s = db.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
            ResultSet r = s.executeQuery(q);
            while (r.next()) {
                u = new User();
                u.setId(r.getInt(COL_ID));
                u.setName(r.getString(COL_NAME));
                members.add(u);
            }
            s.close();
        } catch (SQLException ex) {
            Logger.getLogger(DataServiceImpl.class.getName()).log(Level.SEVERE, null, ex);
        }

        return members.toArray(new User[members.size()]);
    }

    public User[] getDecisionMakers(int projectId, String authToken) throws AuthorizationException
    {
        checkAuthorization(authToken, projectId);

        ArrayList<User> members = new ArrayList<User>();
        try {
            User u;
            String q = "SELECT U.id, U.name FROM project_members PM LEFT JOIN users U ON U.id = PM.user_id ";
            q += "LEFT JOIN role_members RM ON RM.user_id = PM.user_id AND RM.project_id = PM.project_id ";
            q += "LEFT JOIN roles R ON R.id = RM.role_id ";
            q += "WHERE PM.project_id = " + projectId + " ";
            q += "AND R.name = 'decision-maker' ";
            q += "ORDER BY U.name";
            Statement s = db.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
            ResultSet r = s.executeQuery(q);
            while (r.next()) {
                u = new User();
                u.setId(r.getInt(COL_ID));
                u.setName(r.getString(COL_NAME));
                members.add(u);
            }
            s.close();
        } catch (SQLException ex) {
            Logger.getLogger(DataServiceImpl.class.getName()).log(Level.SEVERE, null, ex);
        }

        return members.toArray(new User[members.size()]);
    }

    public User[] findUsers(int projectId, boolean onlyActiveUsers, boolean excludeThisProject, String pattern, String authToken) throws AuthorizationException
    {
        checkAuthorization(authToken, projectId);

        if (pattern == null)
            pattern = "%";
        else
            pattern += '%';

        ArrayList<User> members = new ArrayList<User>();
        try {
            User u;
            String q = "SELECT U.id, U.name FROM users U  ";
            q += "WHERE U.name LIKE ? ";
            if (onlyActiveUsers)
                q += "AND U.is_active = 1 ";
            if (excludeThisProject)
                q += "AND U.id NOT IN ( SELECT user_id FROM project_members WHERE project_id = ? AND is_active = 1 ) ";
            q += "ORDER BY U.name";
            PreparedStatement s = db.prepareStatement(q, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
            s.setString(1, pattern);
            if (excludeThisProject)
                s.setInt(2, projectId);

            ResultSet r = s.executeQuery();
            while (r.next()) {
                u = new User();
                u.setId(r.getInt(COL_ID));
                u.setName(r.getString(COL_NAME));
                members.add(u);
            }
            s.close();
        } catch (SQLException ex) {
            Logger.getLogger(DataServiceImpl.class.getName()).log(Level.SEVERE, null, ex);
        }

        return members.toArray(new User[members.size()]);
    }
    
    public User[] getUsers(int projectId, String authToken) throws AuthorizationException
    {
        checkAuthorization(authToken, projectId);

        ArrayList<User> members = new ArrayList<User>();
        try {
            User u;
            String q = "SELECT user_id, users.name FROM project_members LEFT JOIN users ";
            q += "ON project_members.user_id = users.id ";
            q += "WHERE project_members.project_id = " + projectId + " ";
            q += "ORDER BY users.name";
            PreparedStatement s = db.prepareStatement(q, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
            ResultSet r = s.executeQuery(q);
            while (r.next()) {
                u = new User();
                u.setId(r.getInt(1));
                u.setName(r.getString(2));
                members.add(u);
            }
            s.close();
        } catch (SQLException ex) {
            Logger.getLogger(DataServiceImpl.class.getName()).log(Level.SEVERE, null, ex);
        }

        return members.toArray(new User[members.size()]);
    }

    public ExtendedProjectInfo[] listProjects(String[] keywords, boolean recentOnly, boolean membershipOnly, String authToken) throws AuthorizationException
    {
        // TODO: implement keyword search (works now, but poorly, only for a single keyword)
        // TODO: implement filter for recent projects only
        // TODO: reconsider adding an authorization check
        // TODO: rewrite as SPROC, and revisit MySQL accounts, security

        ArrayList<ExtendedProjectInfo> projects = new ArrayList<ExtendedProjectInfo>();
        try {
            ExtendedProjectInfo p;
            StringBuilder sb = new StringBuilder();
            sb.append("SELECT P.id, P.name, P.is_public, P.url_path, P.allow_copy, member_count, goal_count, option_count ");
            sb.append("FROM projects P ");
            sb.append("LEFT JOIN ( SELECT project_id, count(user_id) AS member_count FROM project_members WHERE is_active = 1 GROUP BY project_id ) AS PM ON PM.project_id = P.id ");
            sb.append("LEFT JOIN ( SELECT project_id, count(id) AS goal_count FROM goals GROUP BY project_id ) AS G ON G.project_id = P.id ");
            sb.append("LEFT JOIN ( SELECT project_id, count(id) AS option_count FROM options GROUP BY project_id ) AS O ON O.project_id = P.id ");
            if ( membershipOnly ) {
                sb.append("LEFT JOIN project_members PMY ON PMY.project_id = P.id ");
                sb.append("LEFT JOIN authentication A ON A.user_id = PMY.user_id AND token = ? AND expires > NOW() ");
                sb.append("WHERE A.user_id IS NOT NULL ");
                // an alternate version, but there is a privilege conflict for calling the function using the read-only account
                // sb.append("WHERE P.id IN ( SELECT project_id FROM project_members WHERE is_active = 1 AND user_id = auth_user_from_session(?) ) ");
            }
            if (keywords != null && keywords.length == 1) {
                /* Check that no bad SQL, so for now we only allow lowercase letters and digits */
                if (keywords[0].matches("[a-z0-9]*")) {
                    if ( membershipOnly )
                        sb.append("AND ");
                    else
                        sb.append("WHERE ");
                    sb.append("P.name LIKE '%" + keywords[0] + "%' ");
                }
            }
            sb.append("GROUP BY P.id ");
            sb.append("ORDER BY P.id DESC; ");
            PreparedStatement s = db.prepareStatement(sb.toString(), ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
            if ( membershipOnly )
                s.setString(1, authToken);

            ResultSet r = s.executeQuery();
            while (r.next()) {
                p = new ExtendedProjectInfo();
                p.setId(r.getInt(COL_ID));
                p.setName(r.getString(COL_NAME));
                p.setPublic(r.getBoolean(3));
                p.setUrlPath(r.getString(4));
                p.setAllowCopy(r.getBoolean(5));
                p.setNumProjectMembers(r.getInt(6));
                p.setNumGoals(r.getInt(7));
                p.setNumOptions(r.getInt(8));
                projects.add(p);
            }
            s.close();
        } catch (SQLException ex) {
            Logger.getLogger(DataServiceImpl.class.getName()).log(Level.SEVERE, null, ex);
        }

        return projects.toArray(new ExtendedProjectInfo[projects.size()]);

//        ExtendedProjectInfo test = new ExtendedProjectInfo();
//        test.setId(5);
//        test.setName("Test Project 5");
//        test.setPublic(true);
//        test.setUrlPath("tp5");
//        test.setNumGoals(20);
//        test.setNumOptions(10);
//        test.setNumProjectMembers(15);
//        return new ExtendedProjectInfo[] { test };
    }

    public NewsItem[] listNewsItems(String authToken) throws AuthorizationException
    {
        ArrayList<NewsItem> news = new ArrayList<NewsItem>();
        try {
            NewsItem n;
            StringBuilder sb = new StringBuilder();
            sb.append("SELECT id, text, author, DATE_FORMAT(pub_date, '%W, %M %D %Y') FROM news ORDER BY pub_date DESC, id DESC; ");
            PreparedStatement s = db.prepareStatement(sb.toString(), ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
            ResultSet r = s.executeQuery();
            while (r.next()) {
                n = new NewsItem();
                n.setId(r.getInt(COL_ID));
                n.setText(r.getString(2));
                n.setAuthor(r.getString(3));
                n.setPubDateStr(r.getString(4));

                news.add(n);
            }
            s.close();
        } catch (SQLException ex) {
            Logger.getLogger(DataServiceImpl.class.getName()).log(Level.SEVERE, null, ex);
        }

        return news.toArray(new NewsItem[news.size()]);
    }
   
    public String getObjectDetails(int id, String object, String authToken) throws AuthorizationException {
    	String biography = "";
    	try {
    		StringBuilder sb = new StringBuilder();
    		if(object.equals("goal")) {
    			sb.append("SELECT details FROM goal_details ");
    			sb.append("WHERE id = " + id + ";");
    		}
    		if(object.equals("option")) {
    			sb.append("SELECT details FROM option_details ");
        		sb.append("WHERE id = " + id + ";");
    		}
    		PreparedStatement s = db.prepareStatement(sb.toString(), ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
    		ResultSet r = s.executeQuery();
    		while(r.next()) {
    			biography = r.getString(1);
    		}
    		s.close();
    	} catch (SQLException ex) {
            Logger.getLogger(DataServiceImpl.class.getName()).log(Level.SEVERE, null, ex);
        }
    	return biography;
    }
    
    public String getObjectPicture(int id, String object, String authToken) throws AuthorizationException {
    	String picture = "";
    	try {
    		StringBuilder sb = new StringBuilder();
    		if(object.equals("goal")) {
    			sb.append("SELECT picture FROM goal_details ");
    			sb.append("WHERE id = " + id + ";");
    		}
    		if(object.equals("option")) {
    			sb.append("SELECT picture FROM option_details ");
    			sb.append("WHERE id = " + id + ";");
    		}
    		PreparedStatement s = db.prepareStatement(sb.toString(), ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
    		ResultSet r = s.executeQuery();
    		while(r.next()) {
    			picture = r.getString(1);
    		}
    		s.close();
    	} catch (SQLException ex) {
            Logger.getLogger(DataServiceImpl.class.getName()).log(Level.SEVERE, null, ex);
        }
    	return picture;
    }
    
    public String[] getObjectInfo(int userId, String authToken) throws AuthorizationException {
    	ArrayList<String> userInfo = new ArrayList<String>();
    	try {
    		StringBuilder sb = new StringBuilder();
    		sb.append("SELECT name, location, occupation, education, contact FROM user_biography ");
    		sb.append("WHERE id = " + userId + ";");
    		PreparedStatement s = db.prepareStatement(sb.toString(), ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
    		ResultSet r = s.executeQuery();
    		while(r.next()) {
    			userInfo.add(r.getString(1));
    			userInfo.add(r.getString(2));
    			userInfo.add(r.getString(3));
    			userInfo.add(r.getString(4));
    			userInfo.add(r.getString(5));
    		}
    		s.close();
    	} catch (SQLException ex) {
            Logger.getLogger(DataServiceImpl.class.getName()).log(Level.SEVERE, null, ex);
        }
    	return userInfo.toArray(new String[userInfo.size()]);
    }

    protected void checkAuthorization(String authToken, int projectId) throws AuthorizationException
    {
        if (!authService.isAuthorizedProjectRead(authToken, projectId))
            throw new AuthorizationException("Not authorized to view this project");
    }
}